package br.edu.ufcg.sig.server.persistence.jdbc;

/**
 * 
 * @author Diego Rodrigues
 * @author Odilon Lima
 *
 */
public class Util {

	final public static String LOGIN = "postgres";
	final public static String PASSWORD = "123456";
	final public static String URL_DRIVER = "org.postgresql.Driver";
	final public static String URL_BD = "jdbc:postgresql://localhost:5432/wikicrimesdb";
	
	final public static String DELICT_TYPE_FURTO = "Furto";
	final public static String DELICT_TYPE_ROUBO = "Roubo";
	
	final public static String DELICT_LOCATION_TYPE_ = "";
	
	final public static String SAVE_DELICT = "INSERT INTO delicts" +
			" (delictType, locationType, date, delictTime, description, usedArmor, qteVictims, qteRobbers, location)" +
			" VALUES(? , ?,to_date(?, 'DD/MM/YYYY'), ?, ?, ?, ?, ?, GeometryFromText(?,-1))";
	
	final public static String HOT_ZONES = "SELECT ASTEXT(BUFFER(d.location, 0.001))" +
										   " FROM delicts d";
	
	final public static String ALL_DELICTS = "SELECT *" +
											 " FROM delicts";
	
	final public static String DELICT_BY_ID = "SELECT *" +
											   " FROM delicts d" +
										       " WHERE d.id_delict = ?";
	
	final public static String DELICTS_BY_DATE = "SELECT d.location" +
												 " FROM delicts d" +
	 								             " WHERE d.data > to_date(?, 'DD/MM/YYYY')" +
	 								               " AND d.data < to_date(?, 'DD/MM/YYYY')";
	
	final public static String DELICTS_BY_TYPE = "SELECT d.location" +
												 " FROM delicts d" + 
												 " WHERE d.delictType = ?";

	final public static String DELICTS_BY_AREA_TYPE = "SELECT COUNT(*)" +
												      " FROM delicts d" +
												      " WHERE INTERSECTS(GeometryFromText(?, -1) ,d.location) " +
												      " AND  d.delictType = ?";	
	
	
}
